﻿CREATE PROCEDURE [dbo].[CalcSalePeek]
(
	@DepartmentId int,					-- код подразделения
	@PeriodBegin smalldatetime,
	@PeriodEnd smalldatetime,
	@ForDaysPeek int = 1,				--количество дней в сплеске
	@PeekCoeff int = 3,					--количество превышения продаж для всплеска
	@UseThirdCategory bit = 0,	--расчитывать всплески для 3-й категории товаров
	@StoreCoef real = 0.03,			--процент расходов по хранению на складе
	@WithAggregate bit = 0			-- =1- расчет с агрегированием товаров у которых одинаковые AssortmentId, RetailPack (точнее DefekturaAggregate = 1)
)
AS
/* Черноуосов ОП 13.07.06 Вычисление всплесков. Вызывается из процедуры dbo.CalcZakaz */
DECLARE @intRet integer, @intErrCode int, @strAction varchar(50)
DECLARE @intPeekNmbr int, @intPrevGoodsId int, @intNowGoodsId int, @datNowDocDate smalldatetime
DECLARE @realNowSaleQntty real, @realPrevSale real, @intSaleDayCount int, @realFullSale real, @intFullDays int
DECLARE @realQnttyForPeek real, @datPrevPeekDate smalldatetime, @intDaysForPeek int, @bitPeekFound bit

	SET NOCOUNT ON
--select @PeriodBegin = '2005-11-01', @PeriodEnd = '2006-01-31'

CREATE TABLE #TGoodsFlow
(
	[GoodsId] [int] NOT NULL ,
	[DocDate] smalldatetime NULL,
	[Turnover] real NULL,
	[SaleQntty] real NULL,
	[SaleSum] [decimal](19, 2) NULL,
	[DayRest] real NULL,
	[FullDays] [int] NULL
)

INSERT INTO #TGoodsFlow (GoodsId, DocDate, Turnover, SaleQntty, SaleSum, DayRest, FullDays)
SELECT GoodsId, DocDate, Turnover, SaleQntty, SaleSum, DayRest, FullDays 
FROM dbo.NotEmptyDays(@DepartmentId, @PeriodBegin, @PeriodEnd, @WithAggregate)

CREATE INDEX [IX_TGoodsGoodsIdDocDate] ON #TGoodsFlow(GoodsId, DocDate) 

IF @UseThirdCategory = 1
	DELETE D FROM #TGoodsFlow D INNER JOIN PR.DefekturaGoods G ON D.GoodsId = G.GoodsId AND (G.DefekturaCategoryId > 3)
ELSE
	DELETE D FROM #TGoodsFlow D INNER JOIN PR.DefekturaGoods G ON D.GoodsId = G.GoodsId AND (G.DefekturaCategoryId > 2)

-- таблица #SalePeek создаётся в процедуре dbo.CalcZakaz
--Поиск всплесков
INSERT INTO #SalePeek (GoodsId, DocDate, SaleQntty, PeekSale, SaleSum, SaleDayCount)
SELECT GoodsId, DocDate, SaleQntty, SaleQntty, SaleSum, 1 
FROM #TGoodsFlow WHERE SaleQntty > 0.0 AND DocDate >= @PeriodBegin

SET @intRet = 1
--расчет продаж за предыдущие дни в  периоде поиска всплеска
WHILE @intRet < @ForDaysPeek
BEGIN
	UPDATE D SET D.PrevSale = D.PrevSale + S.SaleQntty,
	D.SaleDayCount = D.SaleDayCount + 1
	FROM #SalePeek D INNER JOIN #SalePeek S ON D.GoodsId = S.GoodsId AND D.DocDate = dateadd(day, @intRet, S.DocDate )

	SET @intRet = @intRet + 1
END

-- таблица #PeekPeriod создаётся в процедуре dbo.CalcZakaz
--все агрегированные (просуммированные) продажи за период
INSERT INTO #PeekPeriod (GoodsId, SaleQntty, SaleDayCount, FullDays )
SELECT GoodsId, 
sum(CASE WHEN SaleQntty > 0 THEN SaleQntty ELSE 0 END), 
sum(CASE WHEN SaleQntty > 0 THEN 1 ELSE 0 END),
sum(FullDays)
FROM #TGoodsFlow 
WHERE DocDate >= @PeriodBegin
GROUP BY GoodsId

SELECT @intPeekNmbr = 1, @intPrevGoodsId = -999999

DECLARE TheLines CURSOR FAST_FORWARD FOR
SELECT S.GoodsId, S.DocDate, S.SaleQntty, S.PrevSale, S.SaleDayCount, P.SaleQntty, P.FullDays
FROM #SalePeek S INNER JOIN #PeekPeriod P ON S.GoodsId = P.GoodsId
WHERE P.FullDays > @ForDaysPeek --учитываем только те товары где количество дней присутствия больше диапазона поиска всплеска
ORDER BY S.GoodsId, S.DocDate

OPEN TheLines

FETCH NEXT FROM TheLines INTO @intNowGoodsId, @datNowDocDate, @realNowSaleQntty, @realPrevSale, @intSaleDayCount, @realFullSale, @intFullDays

WHILE (@@FETCH_STATUS = 0)
BEGIN

	IF @intNowGoodsId <> @intPrevGoodsId
	BEGIN --сбросить счетчики
		SELECT @realQnttyForPeek = 0.0, @datPrevPeekDate = dateadd(day, -1, @PeriodBegin), @intDaysForPeek = 0
	END

	IF datediff(day, @datPrevPeekDate, @datNowDocDate) > @ForDaysPeek
		SELECT @realQnttyForPeek = @realNowSaleQntty + @realPrevSale, @intDaysForPeek = @intSaleDayCount
	ELSE  --если недавно был всплеск сумму насчитываем итерационно
		SELECT @realQnttyForPeek = @realNowSaleQntty + @realQnttyForPeek, @intDaysForPeek = @intDaysForPeek + 1

	IF @realFullSale = @realQnttyForPeek SET @bitPeekFound = 0  --вся сумма продаж равна текущей сумме продаж - вcплеском не считаем
	ELSE 
		IF (@realQnttyForPeek / ( (@realFullSale - @realQnttyForPeek) / (1.0 * (@intFullDays - @intDaysForPeek) ) ) ) >= @PeekCoeff 
			SET @bitPeekFound = 1
		ELSE 
			SET @bitPeekFound = 0

	IF @bitPeekFound = 1
	BEGIN  --найден всплеск
		UPDATE #SalePeek SET Peek = 1, PeekSale = @realQnttyForPeek, PeekNmbr = @intPeekNmbr,
		SaleDayCount = (CASE WHEN DocDate = @datNowDocDate THEN 1 ELSE 0 END) --для подсчета количества всплесков
		WHERE GoodsId = @intNowGoodsId AND DocDate <= @datNowDocDate 
		AND DocDate > dateadd(day, -1 * @intDaysForPeek, @datNowDocDate ) AND DocDate > @datPrevPeekDate

		SELECT @intPeekNmbr = @intPeekNmbr + 1, @datPrevPeekDate = @datNowDocDate, @realQnttyForPeek = 0.0, @intDaysForPeek = 0 --сбросить счетчики
	END		

	SELECT @intPrevGoodsId = @intNowGoodsId

	FETCH NEXT FROM TheLines INTO @intNowGoodsId, @datNowDocDate, @realNowSaleQntty, @realPrevSale, @intSaleDayCount, @realFullSale, @intFullDays
END --Of WHILE (@@FETCH_STATUS = 0)

CLOSE TheLines
DEALLOCATE TheLines

--select * from #SalePeek where GoodsId = 2391

--удаляем продажи по дням не принадлежащие всплескам
DELETE FROM #SalePeek WHERE Peek = 0

--удаляем обороты не принадлежащие всплескам
DELETE FROM #PeekPeriod WHERE GoodsId NOT IN ( SELECT DISTINCT GoodsId FROM #SalePeek )

UPDATE T SET T.PeekDayUhod = ( 1.0 * S.PeekSale ) / ( 1.0 * S.PeekSaleDayCount ), --определяем средний уход по всплескам в день
T.PeekSaleDayCount = S.PeekSaleDayCount, 
T.PeekSale = S.PeekSale,
T.PeekSaleSum = S.PeekSaleSum,
T.AvgSalePrice = S.AvgSalePrice,
T.PeekCount = S.PeekCount
FROM #PeekPeriod T INNER JOIN (
SELECT GoodsId, sum(SaleQntty) AS PeekSale, count(DocDate) AS PeekSaleDayCount,
sum(SaleSum) AS PeekSaleSum,
avg( SaleSum / SaleQntty ) AS AvgSalePrice,
sum(SaleDayCount) AS PeekCount
FROM  #SalePeek  GROUP BY GoodsId
) S ON T.GoodsId = S.GoodsId 

--вычисляем дисперсию по всплескам 
UPDATE T SET T.PeekDisp = SQRT( P.PeekDisp / (1.0 * T.PeekSaleDayCount) )
FROM #PeekPeriod T INNER JOIN (
SELECT D.GoodsId, SUM( POWER( ( S.SaleQntty - D.PeekDayUhod ), 2) ) AS PeekDisp
FROM #PeekPeriod D INNER JOIN #SalePeek S ON D.GoodsId = S.GoodsId 
GROUP BY  D.GoodsId
) P ON T.GoodsId = P.GoodsId 

--вычисляем количество всплесков
UPDATE T SET T.PeekCount = S.PeekCount, --T.PeekSale = S.PeekSale,
T.PeekSaleSum = S.PeekSaleSum, T.AvgSalePrice = S.AvgSalePrice
FROM #PeekPeriod T INNER JOIN (
SELECT GoodsId, --sum(SaleQntty) AS PeekSale, 
sum(SaleSum) AS PeekSaleSum, 
sum(CASE WHEN SaleDayCount > 0 THEN 1 ELSE 0 END) AS PeekCount,
avg( SaleSum / SaleQntty ) AS AvgSalePrice
FROM  #SalePeek  GROUP BY GoodsId
) S ON T.GoodsId = S.GoodsId 

UPDATE T SET T.ManualCalc = CASE WHEN 100.0 * T.PeekSale / T.SaleQntty > C.MaxPeekValForPeriod THEN 1 ELSE NULL END,
T.OrdinaryCalc = CASE WHEN 100.0 * T.PeekSale / T.SaleQntty < C.MinPeekValForPeriod THEN 1 ELSE NULL END  --обычный расчет
FROM #PeekPeriod T INNER JOIN PR.DefekturaGoods G ON T.GoodsId = G.GoodsId
INNER JOIN CO.DefekturaCategories C ON G.DefekturaCategoryId = C.Id

UPDATE S SET S.MarkUp = ( CASE WHEN IsNull(R.MarkUp,20) = 0 THEN 20 ELSE IsNull(R.MarkUp,20) END)
FROM #PeekPeriod S INNER JOIN (--определение последней (средней) наценки 
	SELECT L.GoodsId, avg(RetailMarkup) AS MarkUp
	FROM (--вычисляем максимальную дату прихода по товарам
		SELECT T.GoodsId, max(D.DocDate) AS DocDate
		FROM #PeekPeriod T INNER JOIN CD.InputLines I ON T.GoodsId = I.GoodsId
		INNER JOIN CA.Documents D ON I.DocId = D.Id
		WHERE D.DocDate <= @PeriodEnd
		GROUP BY T.GoodsId
	) L INNER JOIN CD.InputLines I ON L.GoodsId = I.GoodsId
	INNER JOIN CA.Documents D ON I.DocId = D.Id AND D.DocDate = L.DocDate
	GROUP BY L.GoodsId
) R ON S.GoodsId = R.GoodsId

SET @intRet = datediff(day, @PeriodBegin, @PeriodEnd) --период в днях
--( (@StoreCoef * @intRet / 25.0) / (G.MarkUp * T.PeekCount / 100.0)+ 1.0 )
UPDATE T SET ZV = (T.PeekDayUhod + T.PeekDisp) / ( (4.0 * @StoreCoef * @intRet) / (T.MarkUp * T.PeekCount)+ 1.0 ),
T.PeekStockItem = ( T.PeekSale / T.FullDays ) * AvgSalePrice
FROM #PeekPeriod T
WHERE T.ManualCalc IS NULL AND T.OrdinaryCalc IS NULL AND T.PeekCount > 0

--DELETE FROM #PeekPeriod WHERE ZV IS NULL
--удаляем всплески по товарам которые расчитываются обычным образом 
UPDATE D SET D.Peek = 0
FROM #SalePeek D INNER JOIN #PeekPeriod S ON D.GoodsId = S.GoodsId
WHERE S.OrdinaryCalc = 1

	DROP TABLE #TGoodsFlow

RETURN 0
